package cn.com.weather.nf.repository.impl;

import java.io.Serializable;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import cn.com.weather.nf.entity.TemplateInstances;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.sql.Template;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import cn.com.weather.nf.entity.RecordForm;
import cn.com.weather.nf.entity.RecordWeather;
import cn.com.weather.nf.repository.SimpleRepository;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;

@Repository
public class SimpleRepositoryImpl implements SimpleRepository {

    private static final Logger LOGGER = LoggerFactory.getLogger(SimpleRepositoryImpl.class);

    private static final List<String> PC_OS_NAME_LIST =  Arrays.asList("linux", "windows", "macosx");
    
    
    @PersistenceContext
    private EntityManager entityManager;

    private Session getSession() {

        LOGGER.debug("entityManager :::::: {}", entityManager);


//        Session delegate = (Session) entityManager.getDelegate();
//        SessionFactory sessionFactory = delegate.getSessionFactory();
//        Session session = sessionFactory.openSession();
//        Work work = (conn) -> { };
//        session.doWork(work);
//        session.getTransaction().begin();
//        session.getTransaction().commit();
//        session.close();

        
        return entityManager.unwrap(Session.class);

    }

    @Override
    public <T> T findById(Class<T> type, Serializable id) {

        return this.getSession().get(type, id);
    }

    @Override
    public Integer findListTotal(Class<?> beanClass) {
        Session session = this.getSession();

        String hql = "select count(a) from "+ beanClass.getSimpleName() +" a ";
        Object res = session.createQuery(hql).uniqueResult();

        return Integer.valueOf(res.toString(), 10);

    }

    @Override
    @SuppressWarnings("unchecked")
    public <T> List<T> findListOrderById(Class<T> beanClass, Integer current, Integer pageSize) {

        Session session = this.getSession();

        String hql = "from "+ beanClass.getSimpleName() +" a order by a.id desc ";
        return (List<T>) session.createQuery(hql)
                .setMaxResults(pageSize)
                .setFirstResult((current - 1) * pageSize)
                .list();

    }

    @Override
    public void delete(Class<?> beanClass, Long id) {
        Session session = this.getSession();

        String hql = "delete from "+ beanClass.getSimpleName() + " a where a.id = :id";

        session.createQuery(hql)
                .setLong("id", id)
                .executeUpdate();

    }

    
    /**
     * 
     */
    @Override
    public Integer findRecordFormListTotal(String type, String from, Integer instanceId, String search,
                                           long beginTime, long endTime) {
       
        String sql = " select count(*) from sun_record_form a, sun_template_instances b " + 
                "where a.post_instance = b.id " + 
                "and b.`type` = :type " +
                "and a.create_time between :beginTime and :endTime "
                ;
        if ("pc".equalsIgnoreCase(from)) {
            sql += " and os_name in (:osList)";
        } else {
            sql += " and os_name not in (:osList)";
        }

        if (instanceId != -1) {
            sql += " and b.id = :instanceId ";
        }

        if (StringUtils.hasText(search)) {
            sql += " and a.post_data like :search ";
        }


        Session session = this.getSession();
        SQLQuery sqlQuery = session.createSQLQuery(sql);
        
        sqlQuery.setString("type", type);
        sqlQuery.setParameterList("osList", PC_OS_NAME_LIST);
        sqlQuery.setDate("beginTime", new Date(beginTime));
        sqlQuery.setDate("endTime", new Date(endTime));

        if (instanceId != -1) {
            sqlQuery.setInteger("instanceId", instanceId);
        }

        if (StringUtils.hasText(search)) {
            sqlQuery.setString("search", "%" + search + "%");
        }

        String countResult = sqlQuery.uniqueResult().toString();
        return Integer.valueOf(countResult, 10);
    }

    /**
     * 
     */
    @SuppressWarnings("unchecked")
    @Override
    public List<RecordForm> findRecordFormListOrderById(String type, String from, Integer current, Integer pageSize,
                                                        Integer instanceId, String search,
                                                        long beginTime, long endTime) {

        String sql = " select a.* from sun_record_form a, sun_template_instances b " +
                "where a.post_instance = b.id "+
                "and a.create_time between :beginTime and :endTime "
                ;
        if ("pc".equalsIgnoreCase(from)) {
            sql += " and os_name in (:osList)";
        } else {
            sql += " and os_name not in (:osList)";
        }


        if (instanceId != -1) {
            sql += " and b.id = :instanceId ";
        }

        if (StringUtils.hasText(search)) {
            sql += " and a.post_data like :search ";
        }

        sql += "and b.`type` = :type order by a.id desc ";
        
        Session session = this.getSession();
        SQLQuery sqlQuery = session.createSQLQuery(sql).addEntity(RecordForm.class);

        if (instanceId != -1) {
            sqlQuery.setInteger("instanceId", instanceId);
        }

        if (StringUtils.hasText(search)) {
            sqlQuery.setString("search", "%" + search + "%");
        }

        sqlQuery.setString("type", type)
                .setDate("beginTime", new Date(beginTime))
                .setDate("endTime", new Date(endTime))
                .setParameterList("osList", PC_OS_NAME_LIST)
                .setFirstResult((current - 1 ) * pageSize )
                .setMaxResults(pageSize);
        
        
        List<RecordForm> result = sqlQuery.list();
                
        
        if (result != null && !result.isEmpty()) {
            
            for (RecordForm rf : result) {
                
                Object value = null;
                if (rf.getUser_id() != null) {
                    value = session.createSQLQuery("select `username`, `in_blacklist` from `wi_user_info` where `wi_user_info`.id = :id")
                            .setLong("id", rf.getUser_id())
                            .uniqueResult();
                    if (value != null) {
                        Object[] res = (Object[]) value;
                        rf.setUser_name(res[0].toString());
                        rf.setInBlacklist(Integer.valueOf(res[1].toString(), 10));
                    }
                }
                
                if (rf.getDepartment_id() != null) {
                    
                    value = session.createSQLQuery("select `name` from `sys_office` where `sys_office`.id = :deptId ")
                            .setString("deptId", rf.getDepartment_id())
                            .uniqueResult();
                    if (value != null) {
                        rf.setDepartment_name(value.toString());
                    }
                }
                
                if (rf.getProcess_id() != null) {
                    
                    value = session.createSQLQuery("select `sys_user`.`name` from `sys_user` where `sys_user`.id = :sysUserId ")
                            .setString("sysUserId", rf.getProcess_id())
                            .uniqueResult();
                    if (value != null) {
                        rf.setProcess_name(value.toString());
                    }
                }
            }
        }
        
        return result;
    }
    
    

    /**
     * 
     */
    @Override
    public Integer findRecordWeatherListTotal(String from) {

        String sql = " select count(*) from sun_record_weather a ";
        
        if ("pc".equalsIgnoreCase(from)) {
            sql += " where os_name in (:osList)";
        } else {
            sql += " where os_name not in (:osList)";
        }
        
        Session session = this.getSession();
        SQLQuery sqlQuery = session.createSQLQuery(sql);
        
        sqlQuery.setParameterList("osList", PC_OS_NAME_LIST);
        
        String countResult = sqlQuery.uniqueResult().toString();
        return Integer.valueOf(countResult, 10);
    }

    /**
     * 
     */
    @SuppressWarnings("unchecked")
    @Override
    public List<RecordWeather> findRecordWeatherListOrderById(String from , Integer current, Integer pageSize) {

        String sql = " select a.* from sun_record_weather a ";
        if ("pc".equalsIgnoreCase(from)) {
            sql += " where os_name in (:osList)";
        } else {
            sql += " where os_name not in (:osList)";
        }
        sql += " order by a.id desc ";
        
        Session session = this.getSession();
        SQLQuery sqlQuery = session.createSQLQuery(sql).addEntity(RecordWeather.class);

        sqlQuery.setParameterList("osList", PC_OS_NAME_LIST);

        sqlQuery
        .setFirstResult((current - 1 ) * pageSize )
        .setMaxResults(pageSize);
        
        List<RecordWeather> result = sqlQuery.list();
        if (result != null && !result.isEmpty()) {
            for (RecordWeather rf : result) {
                Object value = null;
                if (rf.getUser_id() != null) {
                    value = session.createSQLQuery("select `username`, `in_blacklist` from `wi_user_info` where `wi_user_info`.id = :id")
                            .setLong("id", rf.getUser_id())
                            .uniqueResult();
                    if (value != null) {
                        Object[] res = (Object[]) value;
                        rf.setUser_name(res[0].toString());
                        rf.setInBlacklist(Integer.valueOf(res[1].toString(), 10));
                    }
                }
                
                if (rf.getDepartment_id() != null) {
                    
                    value = session.createSQLQuery("select `name` from `sys_office` where `sys_office`.id = :deptId ")
                            .setString("deptId", rf.getDepartment_id())
                            .uniqueResult();
                    if (value != null) {
                        rf.setDepartment_name(value.toString());
                    }
                }
                
                if (rf.getProcess_id() != null) {
                    
                    value = session.createSQLQuery("select `sys_user`.`name` from `sys_user` where `sys_user`.id = :sysUserId ")
                            .setString("sysUserId", rf.getProcess_id())
                            .uniqueResult();
                    if (value != null) {
                        rf.setProcess_name(value.toString());
                    }
                }
                
                if (rf.getPost_info() != null) {
                    value = session.createSQLQuery("select `name` from `wi_weather_option` where `id` = :id ")
                            .setLong("id", Long.valueOf(rf.getPost_info(), 10))
                            .uniqueResult();
                    if (value != null) {
                        rf.setInfo_name(value.toString());
                    }
                }
            }
        }
        
        return result;
    }

    /**
     * 
     */
    @Override
    public void update(Object origin) {
       
        Session session = this.getSession();
        session.update(origin);
        
    }


    @Override
    public List<TemplateInstances> findAllTemplateInstances(String type) {

        Assert.notNull(type, "The type must not be null!");
        Session session = this.getSession();

        String sql = "select * from sun_template_instances t where t.type = :type order by id ";

        SQLQuery sqlQuery = session.createSQLQuery(sql)
                .addEntity(TemplateInstances.class);

        sqlQuery.setString("type", type);

        List<TemplateInstances> result = sqlQuery.list();

        return result;

    }
}
